/**
 * 
 */
package climspace.ejb;

import java.io.Serializable;
import java.util.Collection;

import javax.annotation.Resource;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.ejb.Timeout;
import javax.ejb.Timer;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 * @author dnguyen
 * 
 */
@Stateless
@Remote(DBJobTimer.class)
public class DBJobTimerBean implements DBJobTimer {
	private static final long serialVersionUID = 4423607744184932497L;

	private int sInterval = 3600000; // 1 hour

	@Resource
	javax.ejb.TimerService ts;
	
	@PersistenceContext(unitName = "persistenceRDBMS")
	private static EntityManager data;

	public void startTimer(int interval) {
		this.sInterval = interval*60000;
		
		DBJob job = new DBJob();

		// start a timer with an initial delay and an interval of sInterval
		ts.createTimer(sInterval, sInterval, job);

		// show the timers
		Collection<Timer> list = ts.getTimers();
		for (Timer t : list)
			System.out.println("Start timer: " + t.toString());
	}

	public void stopTimer() {
		Collection<Timer> list = ts.getTimers();
		for (Timer t : list) {
			if (t.getInfo() != null && t.getInfo() instanceof DBJob) {
				t.cancel();
				System.out.println("Stop timer: " + t.toString());
			}
		}
	}

	@Timeout
	public void TimeHandler(Timer timer) {
		System.out.print("Running DBTimerJob...");
		DBJob job = (DBJob) timer.getInfo();
		job.execute();
		System.out.print("completed\n");
	}

	/**
	 * This DBJob class is used to clean up the two tables in the DB
	 * WeatherStation & WeatherData. For WeatherData, it will: 
	 * [1] delete data older than a day;
	 * [2] keep only latest record/measurement per station.
	 * For WeatherStation, it will keep only the latest station update
	 */
	public static class DBJob implements Serializable {
		private static final long serialVersionUID = -8073159197695909652L;

		/**
		 * 
		 */
		public void execute() {
			// run clean up query
			// [1] delete data older than a day.
			// [2] keep only latest record per station.
			Query q = data.createNativeQuery(
				"begin; " +
				"delete from weatherdata where entry_datetime < current_date-1 ;"+
				"select d.* " +
				"into tmp_weatherdata " +
				"from weatherdata d,  " +
				"(select max(entry_datetime), max(id) id " +
				"from weatherdata " +
				"group by primary_id) x " +
				"where d.id = x.id; " +
				"delete from weatherdata; " +
				"insert into weatherdata (select * from tmp_weatherdata); " +
				"drop table tmp_weatherdata; " +
				"commit; ");
			System.out.println("DBJob: cleaning weatherdata table");
			q.executeUpdate();
			
			Query cleanWeatherStationQuery = data.createNativeQuery(
				"begin; " +
				"select s.* " +
				"into tmp_weatherstation " +
				"from weatherstation s,  " +
				"(select max(id) id " +
				"from weatherstation " +
				"group by primary_id) x " +
				"where s.id = x.id; " +
				"delete from weatherstation; " +
				"insert into weatherstation (select * from tmp_weatherstation); " +
				"drop table tmp_weatherstation; " +
				"commit; ");
			System.out.println("DBJob: cleaning weatherstation table");
			cleanWeatherStationQuery.executeUpdate();
		}
	}

	@Override
	public void runDBCleanUp() {
		DBJob job = new DBJob();
		job.execute();
	}
}
